package com.ynjt.db;

import org.apache.commons.lang3.CharUtils;
import org.apache.commons.lang3.StringUtils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

public class SQLConstructor {

    /**
     * create table if not exists table_name(id int(8) auto_increment, name text, value float(8,2), date timestamp, primary key(id))
     *
     * @return
     */
    public String createTable(Object o){
        StringBuilder sb = new StringBuilder("create table if not exists ")
        .append(getTableName(o))
        .append("(")
        .append(proMapToString(getPropertyMap(o)))
        .append(")");
        return sb.toString();
    }

    /**
     * delete from table_name where id=*；
     *
     * @return
     */
    public String delete(Object o) throws Exception {
        Map<String,Object> proMap = new HashMap<>();
        proMap = getObjKeyValueMap(o);
        StringBuffer sb = new StringBuffer("delete from ");
        sb.append(getTableName(o));
        sb.append(" where id=");
        sb.append(proMap.get("id"));
        return sb.toString();
    }

    /**
     * select name, value, id, date from table_name where name like '%****%'；
     *
     * @return
     */
    public String query(Object o) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        Map<String,Object> proMap = new HashMap<>();
        proMap = getObjKeyValueMap(o);
        StringBuilder stringBuilder = new StringBuilder("select ");
        stringBuilder.append(getProList(getPropertyMap(o)));
        stringBuilder.append(" from ");
        stringBuilder.append(getTableName(o));
        stringBuilder.append(" where ");
        stringBuilder.append(getQueryMapStr(proMap));
        return stringBuilder.toString();
    }

    /**
     * update table_name set name='****', value=***, date='*********'  where id=*；
     *
     * @return
     */
    public String update(Object o) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        Map<String,Object> proMap = new HashMap<>();
        proMap = getObjKeyValueMap(o);
        StringBuilder stringBuilder = new StringBuilder("update ");
        stringBuilder.append(getTableName(o));
        stringBuilder.append(" set ");
        stringBuilder.append(getQueryMapStr(proMap));
        stringBuilder.append(" where id=");
        stringBuilder.append(proMap.get("id"));
        return stringBuilder.toString();
    }

    private String proMapToString(Map<String,String> proMap){
        StringBuilder stringBuilder = new StringBuilder("");
        Iterator<Map.Entry<String,String>> iterator = proMap.entrySet().iterator();
        while (iterator.hasNext()){
            Map.Entry<String,String> itMap = iterator.next();
            stringBuilder.append(propertyToField(itMap.getKey()));
            stringBuilder.append(" ");
            stringBuilder.append(itMap.getValue());
            if (itMap.getKey().equals("id")){
                stringBuilder.append(" auto_increment");
            }
                stringBuilder.append(", ");
            if (!iterator.hasNext()){
                stringBuilder.append("primary key(id)");
            }
        }
        return stringBuilder.toString();
    }

    private String getProList(Map<String,String> proMap){
        StringBuilder stringBuilder = new StringBuilder("");
        Iterator<Map.Entry<String,String>> iterator = proMap.entrySet().iterator();
        while (iterator.hasNext()){
            Map.Entry<String,String> itMap = iterator.next();
            stringBuilder.append(propertyToField(itMap.getKey()));
            stringBuilder.append(", ");
        }
        return stringBuilder.toString();
    }

    private Map<String, String> getPropertyMap(Object o) {
        StringBuilder sqlStr = new StringBuilder("");
        Field[] fields = o.getClass().getDeclaredFields();
        Map<String,String> proMap = new HashMap<>();
        for (Field field : fields){
            proMap.put(field.getName(),getDBDataType(field));
        }
        return proMap;
    }

//    private static final Map<Class, String> JAVA_MYSQL_MAP = new HashMap<>();
//    static{
//        JAVA_MYSQL_MAP.put(int.class, "int");
//        JAVA_MYSQL_MAP.put(Integer.class, "int");
//    }

    private String getDBDataType(Field field){
        //System.out.println(field.getType());
        if (field.getType().toString().equals("int")){
            return "int(10)";
        }else if (field.getType().toString().equals("char")){
            return "varchar(100)";
        }else if(field.getType().toString().equals("byte")){
            return "varbinary(100)";
        }else if (field.getType().toString().equals("boolean")) {
            return "bit";
        }else if (field.getType().toString().equals("float")){
            return "float(10,2)";
        }else if (field.getType().toString().equals("double")){
            return "double(16,4)";
        }else if (field.getType().toString().equals("short")){
            return "int(16)";
        }else if (field.getType().toString().equals("char")){
            return "char(16)";
        }else if (field.getType().toString().equals("String")){
            return "text";
        }
        return "varchar(100)";
    }

    /**
     * StudentTest --> studenttest ? --> student_test
     * TODO modify this
     * @param o
     * @return
     */
    private String getTableName(Object o) {
        String className = o.getClass().getName();
        className = className.substring(className.lastIndexOf(".")+1).toLowerCase();
        return className;
    }

    /**
     * insert into table_name(name, value, date ) values('****', ****, '**********' )；
     *
     * @return
     */
    public String add(Object o) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        Map<String,Object> proMap = new HashMap<>();
        proMap = getObjKeyValueMap(o);
        StringBuilder stringBuilder = new StringBuilder("insert into ");
        stringBuilder.append(getTableName(o));
        stringBuilder.append("(");
        stringBuilder.append(getMapKey(proMap));
        stringBuilder.append(")");
        stringBuilder.append(" values(");
        stringBuilder.append(getMapValue(proMap));
        stringBuilder.append(")");
        return stringBuilder.toString();
    }

    private String getMapValue(Map<String,Object> proMap) {
        StringBuilder stringBuilder = new StringBuilder("");
        Iterator<Map.Entry<String, Object>> entries = proMap.entrySet().iterator();
        while (entries.hasNext()) {
            Map.Entry<String, Object> entry = entries.next();
            stringBuilder.append(valueToStr(entry.getValue()));
            if (entries.hasNext()){
                stringBuilder.append(", ");
            }
        }
        return stringBuilder.toString();
    }

    private String valueToStr(Object o){
        if(o instanceof String){
            return "'"+o+"'";
        }
        if (o instanceof Character){
            return "'"+o+"'";
        }
        return o.toString();
    }

    private String getMapKey(Map<String,Object> proMap) {
        StringBuilder stringBuilder = new StringBuilder("");
        Iterator<Map.Entry<String, Object>> entries = proMap.entrySet().iterator();
        while (entries.hasNext()) {
            Map.Entry<String, Object> entry = entries.next();
            stringBuilder.append(entry.getKey());
            if (entries.hasNext()){
                stringBuilder.append(", ");
            }
        }
        return stringBuilder.toString();
    }

    /**
     * get obj property key - value map
     * @param o
     * @return
     */
    public Map<String,Object> getObjKeyValueMap(Object o) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        Map<String, Object> proMap = new HashMap<>();
        for (Field field : o.getClass().getDeclaredFields()){
            Method m = o.getClass().getMethod(
                    "get" + toMethodName(field.getName()));
            Object val = m.invoke(o);
            proMap.put(propertyToField(field.getName()), val);
        }
        return proMap;
    }

    private static String toMethodName(String fildeName){
        return fildeName.substring(0,1).toUpperCase()+fildeName.substring(1);
    }

    private String getQueryMapStr(Map<String,Object> proMap) {
        StringBuilder stringBuilder = new StringBuilder("");
        Iterator<Map.Entry<String, Object>> entries = proMap.entrySet().iterator();
        while (entries.hasNext()) {
            Map.Entry<String, Object> entry = entries.next();
            stringBuilder.append(entry.getKey());
            stringBuilder.append("=");
            stringBuilder.append(valueToStr(entry.getValue()));
            if (entries.hasNext()){
                stringBuilder.append(", ");
            }
        }
        return stringBuilder.toString();
    }

    /**
     * Object property to sql property string
     * @param property
     * @return
     */
    public static String propertyToField(String property) {
        if (null == property) {
            return "";
        }
        char[] chars = property.toCharArray();
        StringBuffer sb = new StringBuffer();
        for (char c : chars) {
            if (CharUtils.isAsciiAlphaUpper(c)) {
                sb.append("_" + StringUtils.lowerCase(CharUtils.toString(c)));
            } else {
                sb.append(c);
            }
        }
        return sb.toString();
    }

    /**
     * sql property string to Object property
     * @param field
     * @return
     */
    public static String fieldToProperty(String field) {
        if (null == field) {
            return "";
        }
        char[] chars = field.toCharArray();
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < chars.length; i++) {
            char c = chars[i];
            if (c == '_') {
                int j = i + 1;
                if (j < chars.length) {
                    sb.append(StringUtils.upperCase(CharUtils.toString(chars[j])));
                    i++;
                }
            } else {
                sb.append(c);
            }
        }
        return sb.toString();
    }


}
